﻿//************************************************************************************************************
//文件名称(File Name)：EstimationGuestMana.cs
//功能描述(Description)：标准客户资产评估
//数据表(Tables)：
//作者(Author)：
//日期(Create Date)：20130222
//参考文档(Reference)(可选)：
//引用(Using) (可选)﹕
//************************************************************************************************************

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DbAdapter.sqldbAdapter;
using System.Data;

namespace AnalyseCrmMana
{
    public class EstimationGuestMana
    {
        /// <summary>
        /// 用户登录名称
        /// </summary>
        public string username
        {
            get;
            set;
        }

        /// <summary>
        /// 客户姓名
        /// </summary>
        public string guestname
        {
            get;
            set;
        }
        /// <summary>
        /// 联系号码
        /// </summary>
        public string relphone
        {
            get;
            set;
        }
        /// <summary>
        /// 证件号码
        /// </summary>
        public string crenumber
        {
            get;
            set;
        }
        /// <summary>
        /// 客户城市（中文）
        /// </summary>
        public string guestcity
        {
            get;
            set;
        }
        /// <summary>
        /// 客户城市（拼音）
        /// </summary>
        public string guestcitypy
        {
            get;
            set;
        }
        /// <summary>
        /// 年龄
        /// </summary>
        public int guestage
        {
            get;
            set;
        }
        /// <summary>
        /// 性别	
        /// </summary>
        public string guestsex
        {
            get;
            set;
        }
        /// <summary>
        /// 家庭地址
        /// </summary>
        public string homeaddr
        {
            get;
            set;
        }
        /// <summary>
        /// 金融资产
        /// </summary>
        public decimal assets
        {
            get;
            set;
        }
        /// <summary>
        /// 车价
        /// </summary>
        public decimal carvalue
        {
            get;
            set;
        }
        /// <summary>
        /// 资产估算组合
        /// </summary>
        public decimal assetsgroup
        {
            get;
            set;
        }
        /// <summary>
        /// 估算资产
        /// </summary>
        public decimal estimatvalue
        {
            get;
            set;
        }
        /// <summary>
        /// 增加估算客户
        /// </summary>
        /// <param name="estimat"></param>
        /// <returns></returns>
        public int AndNewEstimationGuest(string[] estimat)
        {
            sqldb db = new sqldb();
            for (int arri = 0; arri < estimat.Length; arri++)
            {
                switch (arri)
                {
                    case 6:
                        if (estimat[arri] == "undefined")
                        {
                            estimat[arri] = "0";
                        }
                        break;
                    case 9:
                    case 10:
                        if (estimat[arri] == "undefined")
                        {
                            estimat[arri] = "0.00";
                        }
                        break;
                    default:
                        if (estimat[arri] == "undefined")
                        {
                            estimat[arri] = "";
                        }
                        break;
                }
            }
            estimat[11] = EvaluationAssetCombination(estimat[8], 
                          Convert.ToDecimal(estimat[9]), Convert.ToDecimal(estimat[10])).ToString();
            return db.ExecuteNonQuery(sqlconn.GetConnStr(), "Proc_EstimationGuest", estimat);            
        }
        /// <summary>
        /// 删除估算客户
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int DeleteEstimatGuestForUser(string user)
        {
            string sql = "delete from Tbt_EstimationGuest where USERNAME = '" + user + "'";
            sqldb db = new sqldb();
            return db.ExecuteNonQuery(sqlconn.GetConnStr(), CommandType.Text, sql);
        }

        /// <summary>
        /// 1、只有房产项的估算
        /// 2、只有车产项的估算
        /// 3、只有金融资产项的估算
        /// 4、房产+车产项的估算
        /// 5、房产+金融资产项的估算
        /// 6、车产+金融资产项的估算
        /// 7、金融资产+房产+车产项的估算
        /// </summary>
        /// <param name="houser"></param>
        /// <param name="jinrong"></param>
        /// <param name="car"></param>
        /// <returns></returns>
        public int EvaluationAssetCombination(string houser, decimal jinrong, decimal car)
        {
            int combination = 0;
            if ((houser != "") & (jinrong == 0.00M) & (car == 0.00M))
            {
                combination = 1;
            }
            else if ((houser == "") & (jinrong == 0.00M) & (car != 0.00M))
            {
                combination = 2;
            }
            else if ((houser == "") & (car == 0.00M) & (jinrong != 0.00M))
            {
                combination = 3;
            }
            else if ((houser != "") & (jinrong == 0.00M) &(car != 0.00M))
            {
                combination = 4;
            }
            else if ((houser != "") & (jinrong != 0.00M) & (car == 0.00M))
            {
                combination = 5;
            }
            else if ((houser == "") & (jinrong != 0.00M) & (car != 0.00M))
            {
                combination = 6;
            }
            else if ((houser != "") & (jinrong != 0.00M) & (car != 0.00M))
            {
                combination = 7;
            }
            return combination;
        }

        /// <summary>
        /// 获取标准户资产评估(分页)
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public IList<EstimationGuestMana> GetEstimationGuestList(string param)
        {
            string[] selcon = param.Split('|');
            int arri = selcon.Length;
            int startrow = Convert.ToInt32(selcon[arri-1]) * (Convert.ToInt32(selcon[arri-2]) - 1);
            int endrow = Convert.ToInt32(selcon[arri-1]) * Convert.ToInt32(selcon[arri-2]) + 1;
            StringBuilder conditionbul = new StringBuilder();
            for (int i = 0; i < selcon.Length - 2; i++)
            {
                string[] selconsub = selcon[i].Split(',');
                conditionbul.Append(" or (A.ASSETSGROUP = " + selconsub[0] + " and A.ALLVALUE > " + selconsub[1] + " and A.ALLVALUE < " + selconsub[2] + ")");
            }
            string sql = "select B.* from (select ROW_NUMBER() OVER(ORDER BY A.CERNUMBER DESC) rownumber,USERNAME,GUESTNAME,RELPHONE,CERNUMBER,GUESTCITY,GUESTCITYPY,GUESTAGE,"
                       + "GUESTSEX,HOMEADDR,ASSETS,CARVALUE,ASSETSGROUP,ALLVALUE from Tbt_EstimationGuest A where (1<>1)"
                       + conditionbul.ToString() + ") B WHERE  B.rownumber >" + startrow + " and B.rownumber < " + endrow;
            sqldb db = new sqldb();
            DataSet ds = db.ExecuteDataset(sqlconn.GetConnStr(), CommandType.Text, sql);
            IList<EstimationGuestMana> egmanalist = null;
            if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                egmanalist = new List<EstimationGuestMana>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    EstimationGuestMana egmana = new EstimationGuestMana();
                    egmana.username = dr["USERNAME"].ToString();
                    egmana.guestname = dr["GUESTNAME"].ToString();
                    egmana.relphone = dr["RELPHONE"].ToString();
                    egmana.crenumber = dr["CERNUMBER"].ToString();
                    egmana.guestcity = dr["GUESTCITY"].ToString();
                    egmana.guestcitypy = dr["GUESTCITYPY"].ToString();
                    egmana.guestage = Convert.ToInt32(dr["GUESTAGE"]);
                    egmana. guestsex = dr["GUESTSEX"].ToString();
                    egmana.homeaddr = dr["HOMEADDR"].ToString();
                    egmana.assets = Convert.ToDecimal(dr["ASSETS"]);
                    egmana.carvalue = Convert.ToDecimal(dr["CARVALUE"]);
                    egmana.assetsgroup = Convert.ToInt32(dr["ASSETSGROUP"]);
                    egmana.estimatvalue = Convert.ToDecimal(dr["ALLVALUE"]);
                    egmanalist.Add(egmana);
                }
            }
            return egmanalist;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public int GetEstimationGuestCount(string param)
        {
            string[] selcon = param.Split('|');
            StringBuilder conditionbul = new StringBuilder();
            for (int i = 0; i < selcon.Length - 2; i++)
            {
                string[] selconsub = selcon[i].Split(',');
                conditionbul.Append(" or (A.ASSETSGROUP = " + selconsub[0] + " and A.ALLVALUE > " + selconsub[1] + " and A.ALLVALUE < " + selconsub[2] + ")");
            }
            string sql = "select count(1) from Tbt_EstimationGuest A where (1<>1)" + conditionbul.ToString();
            sqldb db = new sqldb();
            object rowcount = db.ExecuteScalar(sqlconn.GetConnStr(), CommandType.Text, sql);
            return Convert.ToInt32(rowcount);
        }

        /// <summary>
        /// 获取标准户资产评估(不分页)
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public IList<EstimationGuestMana> GetAllEstimationGuest(string param)
        {
            string[] selcon = param.Split('|');
            StringBuilder conditionbul = new StringBuilder();
            for (int i = 0; i < selcon.Length - 1; i++)
            {
                string[] selconsub = selcon[i].Split(',');
                conditionbul.Append(" or (A.ASSETSGROUP = " + selconsub[0] + " and A.ALLVALUE > " + selconsub[1] + " and A.ALLVALUE < " + selconsub[2] + ")");
            }
            string sql = "select B.* from (select ROW_NUMBER() OVER(ORDER BY A.CERNUMBER DESC) rownumber,USERNAME,GUESTNAME,RELPHONE,CERNUMBER,GUESTCITY,GUESTCITYPY,GUESTAGE,"
                       + "GUESTSEX,HOMEADDR,ASSETS,CARVALUE,ASSETSGROUP,ALLVALUE from Tbt_EstimationGuest A where (1<>1)"
                       + conditionbul.ToString() + ") B";
            sqldb db = new sqldb();
            DataSet ds = db.ExecuteDataset(sqlconn.GetConnStr(), CommandType.Text, sql);
            IList<EstimationGuestMana> egmanalist = null;
            if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                egmanalist = new List<EstimationGuestMana>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    EstimationGuestMana egmana = new EstimationGuestMana();
                    egmana.username = dr["USERNAME"].ToString();
                    egmana.guestname = dr["GUESTNAME"].ToString();
                    egmana.relphone = dr["RELPHONE"].ToString();
                    egmana.crenumber = dr["CERNUMBER"].ToString();
                    egmana.guestcity = dr["GUESTCITY"].ToString();
                    egmana.guestcitypy = dr["GUESTCITYPY"].ToString();
                    egmana.guestage = Convert.ToInt32(dr["GUESTAGE"]);
                    egmana.guestsex = dr["GUESTSEX"].ToString();
                    egmana.homeaddr = dr["HOMEADDR"].ToString();
                    egmana.assets = Convert.ToDecimal(dr["ASSETS"]);
                    egmana.carvalue = Convert.ToDecimal(dr["CARVALUE"]);
                    egmana.assetsgroup = Convert.ToInt32(dr["ASSETSGROUP"]);
                    egmana.estimatvalue = Convert.ToDecimal(dr["ALLVALUE"]);
                    egmanalist.Add(egmana);
                }
            }
            return egmanalist;
        }

        /// <summary>
        /// 进行资产估算
        /// </summary>
        /// <returns></returns>
        public int builderEstimationGues(string chartno,string uname,int flag)
        {
            object[] param = new object[] { uname,chartno, flag };
            sqldb db = new sqldb();
            return db.ExecuteNonQuery(sqlconn.GetConnStr(), "Proc_EstimationGuestCount", param);
        }

        /// <summary>
        /// 是否存在图表
        /// </summary>
        /// <param name="chartno"></param>
        /// <param name="uname"></param>
        /// <returns></returns>
        public int IsExistsEstimationChart(string chartno, string uname)
        {
            string sql = "select count(1) from Tbt_ChartSeries where CHARTNO = '" 
                        + chartno + "' and USERNAME = '" + uname + "'";
            sqldb db = new sqldb();
            object rowcount = db.ExecuteScalar(sqlconn.GetConnStr(), CommandType.Text, sql);
            return Convert.ToInt32(rowcount);
        }

        /// <summary>
        /// 是否存在评估客户
        /// </summary>
        /// <param name="uname"></param>
        /// <returns></returns>
        public int IsExistsEstimationGuest(string uname)
        {
            string sql = "select count(1) from Tbt_EstimationGuest where USERNAME = '" + uname + "'";
            sqldb db = new sqldb();
            object rowcount = db.ExecuteScalar(sqlconn.GetConnStr(), CommandType.Text, sql);
            return Convert.ToInt32(rowcount);
        }
    }
}
